package com.cksys.achievement.utils;

import com.cksys.achievement.entity.Result;
import com.cksys.achievement.entity.Type;
import com.cksys.achievement.entity.User;
import com.cksys.achievement.service.ITypeService;
import com.cksys.achievement.service.IUserService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.safety.Whitelist;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @ClassName: ExcelUtil
 * @author: zyx
 * @E-mail: 1377631190@qq.com
 * @DATE: 2020/3/11 22:57
 */
@Component
public class ExcelUtil {

    @Autowired
    ITypeService typeService;

    @Autowired
    IUserService userService;

    /**
     * 标题的宽度，单位为 256字符宽度
     */
    public static final int TITLE_WIDTH_X = 15;
    public static final int TITLE_ROW_HEIGHT = 35;
    public static final int CONTENT_ROW_HEIGHT = 18;
    public static final int CONTENT_START_ROW = 2;
    public static final int ALL_ROWS = 502;

    // 导出部分开始

    public void download(HttpServletResponse response, Workbook workbook, String fileName) {
        response.reset();
        BufferedOutputStream os = null;
        try {
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));
            os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                assert os != null;
                os.flush();
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public void setContent(List<Result> results, Integer titleLength, Sheet sheet, CellStyle style) {
        for (int i = 0; i < results.size(); i++) {
            Row row = sheet.createRow(i + 2);
            for (int j = 0; j < titleLength; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(style);
                Result result = results.get(i);
                if (j == 0) {
                    cell.setCellValue(result.getName());
                }
                if (j == 1) {
                    cell.setCellValue(result.getUserList().get(0).getRealName());
                }
                if (j == 2) {
                    if (result.getType() != null) {
                        cell.setCellValue(result.getType().getName());
                    } else {
                        cell.setCellValue("");
                    }
                }
                if (j == 3) {
                    if (result.getLink() != null) {
                        cell.setCellValue(result.getLink());
                    } else {
                        cell.setCellValue("");
                    }
                }
                if (j == 4) {
                    if (result.getGetTime() != null) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                        cell.setCellValue(sdf.format(result.getGetTime()));
                    } else {
                        cell.setCellValue("");
                    }
                }
                if (j == 5) {
                    if (result.getInfo() != null) {
                        cell.setCellValue(result.getInfo());
                    } else {
                        cell.setCellValue("");
                    }
                }
                if (j == 6) {
                    List<User> userList = result.getUserList();
                    userList.remove(0);
                    List<String> userNames = userList.stream().map(User::getRealName).collect(Collectors.toList());
                    if (userNames.size() > 0) {
                        String s = StringUtils.arrayToDelimitedString(userNames.toArray(), ",");
                        cell.setCellValue(s);
                    } else {
                        cell.setCellValue("");
                    }
                }
            }
        }
    }

    public void setContent(String[] titles, Sheet sheet, CellStyle style) {
        Row row = sheet.createRow(1);
        row.setHeightInPoints(CONTENT_ROW_HEIGHT);
        for (int i = 0; i < titles.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(style);
        }
    }

    public void setTitle(int col, String title, Sheet sheet, CellStyle cellStyle) {
        Row row = sheet.createRow(0);
        row.setHeightInPoints(TITLE_ROW_HEIGHT);

        Cell cell = row.createCell(0);
        cell.setCellValue(title);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));
    }

    public XSSFWorkbook createWorkBook() {
        return new XSSFWorkbook();
    }


    public CellStyle getTitleType(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();

        font.setFontName("等线 Light");
        font.setFontHeightInPoints((short) 18);
        font.setBold(true);
        cellStyle.setFont(font);

        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }

    /**
     * 设置总体表格样式
     */
    public CellStyle getContentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();

        font.setFontName("等线");
        font.setFontHeightInPoints((short) 10);
        font.setBold(true);
        style.setFont(font);

        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    public CellStyle getResultContentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();

        font.setFontName("等线");
        font.setFontHeightInPoints((short) 10);
        font.setBold(false);
        style.setFont(font);

        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    // 导出部分结束

    // 导入部分开始

    /**
     * 根据file导入成果列表
     * @param inputStream file的输入流
     */
    public List<Result> importResult(InputStream inputStream) throws IOException {
        XSSFWorkbook workBook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workBook.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        ArrayList<Result> results = new ArrayList<>();

        for (int i = 2; i < rows; i++) {
            XSSFRow row = sheet.getRow(i);
            if (null != row) {
                Result result = new Result();
                short cellNum = row.getLastCellNum();
                for (int j = 0; j < cellNum; j++) {
                    XSSFCell cell = row.getCell(j);
                    if (null != cell) {
                        if (j == 0) {
                            String name = cell.getStringCellValue();
                            if (null != name && !"".equals(name)) {
                                result.setName(Jsoup.clean(name, Whitelist.relaxed()));
                            }
                        } else if(j == 1) {
                            double numericCellValue = cell.getNumericCellValue();
                            User user = userService.getById((int) numericCellValue);
                            if (null != user) {
                                result.setUId(user.getId());
                            }
                        } else if (j == 2) {
                            String typeName = cell.getStringCellValue();
                            if (null != typeName && !"".equals(typeName)) {
                                Type type = typeService.findTypeByName(typeName);
                                if (null != type) {
                                    result.setTypeId(type.getId());
                                }
                            }
                        } else if (j == 3) {
                            String link = cell.getStringCellValue();
                            if (null != link && !"".equals(link)) {
                                result.setLink(Jsoup.clean(link, Whitelist.relaxed()));
                            }
                        } else if (j == 4) {
                            Date getTime = cell.getDateCellValue();
                            if (null != getTime) {
                                result.setGetTime(getTime);
                            }
                        } else if (j == 5) {
                            String info = cell.getStringCellValue();
                            if (null != info && !"".equals(info)) {
                                result.setInfo(Jsoup.clean(info, Whitelist.relaxed()));
                            }
                        } else if (j == 6) {
                            CellType cellTypeEnum = cell.getCellTypeEnum();
                            if (cellTypeEnum.equals(CellType.STRING)) {
                                String ids = cell.getStringCellValue();
                                ids = Jsoup.clean(ids, Whitelist.relaxed());
                                String[] idLists = ids.split(",");
                                List<Integer> idList = new ArrayList<>();
                                for (String id : idLists) {
                                    idList.add(Integer.valueOf(id));
                                }
                                List<User> userList = (List<User>) userService.listByIds(idList);
                                if (userList.size() == idList.size()) {
                                    result.setUserList(userList);
                                }
                            } else if (cellTypeEnum.equals(CellType.NUMERIC)) {
                                User userList = userService.getById((int) cell.getNumericCellValue());
                                List<User> users = new ArrayList<>();
                                users.add(userList);
                                result.setUserList(users);
                            }

                        }
                    }
                }
                results.add(result);
            }
        }
        return results;
    }

    // 导入部分结束

    // 验证部分开始


    public void setTimeValidation(Sheet sheet, int startRow, int endRow, int startCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();

        LocalDate localDate = LocalDate.now();
        int year = localDate.getYear();
        int month = localDate.getMonth().getValue();
        int day = localDate.getDayOfMonth();
        CellRangeAddressList rangeAddressList = new CellRangeAddressList(startRow, endRow, startCol, endCol);
        DataValidationConstraint dateConstraint = helper.createDateConstraint(
                DataValidationConstraint.OperatorType.BETWEEN,
                "date(1900,1,1)", "date(" + year + "," + month + "," + day + ")", "yyyy/MM/dd");
        DataValidation dataValidation = helper.createValidation(dateConstraint,
                rangeAddressList);

        dataValidation.createPromptBox("", "请以/分割年月日");
        dataValidation.createErrorBox("出现错误了哦！", "改日期不合法！");
        dataValidation.setShowErrorBox(true);
        dataValidation.setShowPromptBox(true);
        dataValidation.setEmptyCellAllowed(false);

        sheet.addValidationData(dataValidation);
    }

    public void setTypeValidation(Sheet sheet, String[] textList, int startRow, int endRow, int startCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();

        CellRangeAddressList rangeAddress = new CellRangeAddressList(startRow, endRow, startCol, endCol);
        DataValidation dataValidation = helper.createValidation(helper.createExplicitListConstraint(textList), rangeAddress);

        dataValidation.createPromptBox("", "请在下拉菜单选择");
        dataValidation.createErrorBox("出现错误了哦！", "请在下拉菜单中选择！");
        dataValidation.setShowErrorBox(true);
        dataValidation.setShowPromptBox(true);
        dataValidation.setEmptyCellAllowed(false);

        sheet.addValidationData(dataValidation);
    }

    public void setLengthValidation(Sheet sheet, Integer length, int startRow, int endRow, int startCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();

        CellRangeAddressList rangeAddress = new CellRangeAddressList(startRow, endRow, startCol, endCol);
        DataValidation dataValidation = helper.createValidation(helper.createTextLengthConstraint(DataValidationConstraint.OperatorType.BETWEEN, "1", String.valueOf(length))
                , rangeAddress);

        dataValidation.createPromptBox("", "不能为空且不能超过" + length);
        dataValidation.createErrorBox("出现错误了哦！", "不能为空且不能超过" + length);
        dataValidation.setShowErrorBox(true);
        dataValidation.setShowPromptBox(true);
        dataValidation.setEmptyCellAllowed(false);

        sheet.addValidationData(dataValidation);
    }

    // 验证部分结束

}
